import pandas as pd
from sqlalchemy import create_engine
import logging  
import time
# 初始化数据库连接，使用pymysql模块
# MySQL的用户：root, 密码:147369, 端口：3306,数据库：FlightDB
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/FlightDB')
import pymysql.cursors
connect=pymysql.Connect(
                host='localhost',
                port=3306,
                user='root',
                passwd='123456',
                db='FlightDB',
                charset='utf8'
            )
loggingfilename = str(time.time())+".log"

logging.basicConfig(level=logging.DEBUG  # 设置日志输出格式
                    , filename=loggingfilename  # log日志输出的文件位置和文件名
                    , filemode="w"  # 文件的写入格式，w为重新写入文件，默认是追加
                    , format="%(asctime)s - %(name)s - %(levelname)-9s - %(filename)-8s : %(lineno)s line - %(message)s", datefmt="%Y-%m-%d %H:%M:%S"
                    )
# 管理员登录
def login():
    flag = 1
    print("-------航班机票管理系统登录界面-------")
    while flag==1:
        usernumber=input("账号：")
        pwd=input("密码：")
        sql = "SELECT * FROM  users WHERE UserNumber=%(UserNumber)s and UserPassword=%(UserPassword)s"
        df = pd.read_sql_query(sql, engine, params={"UserNumber": usernumber,"UserPassword":pwd})
        if len(df) >0:
            flag = 0
            print("登录成功！")
            logging.info("登录成功！用户："+usernumber)
            index()
        else:
            print("用户名或密码错误！请重新输入")

# 查询
def search():
    sql = ''' SELECT Tno AS '编号',Hno AS '航班号',StartAdress AS '出发地',EndAdress AS
            '目的地',StratTime AS '出发日期',EndTime AS '到达日期',Price AS '票价',TicketType AS '舱位' FROM tickets; '''
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)
    pd.set_option('display.colheader_justify', 'center')
    df = pd.read_sql_query(sql,engine)
    logging.info("查询成功！")
    print(df)

# 修改
def update():
    a=1
    while a==1:
        id = input("请输入你要修改的机票编号：")
        sql="SELECT * FROM  tickets WHERE Tno=%(pa)s"
        df = pd.read_sql_query(sql, engine,params={"pa":id})
        if len(df)<=0:
            print("没有该票信息，请重新输入！")
        else:
            a=0
            print("已查询到该票信息：")
            sql=''' SELECT Tno AS 机票编号,Hno AS 航班号,StartAdress AS 出发地,EndAdress AS 
            目的地,StratTime AS 出发日期,EndTime AS 到达日期,Price AS 票价,TicketType AS 舱位 FROM tickets WHERE Tno=%(pa)s;  '''
            df = pd.read_sql_query(sql, engine, params={"pa": id})
            print(df)
            print("请输入修改后的信息：")
            Hno=input("航班号：")
            StartAdress = input("出发地：")
            EndAdress = input("目的地：")
            StratTime = input("起飞时间：")
            EndTime = input("到达时间：")
            Price = input("票价：")
            cursor = connect.cursor()
            sql = "UPDATE tickets SET Hno='%s',StartAdress='%s',EndAdress='%s',StratTime='%s',EndTime='%s',Price='%s'  WHERE  Tno='%s'"
            data = (Hno,StartAdress,EndAdress,StratTime,EndTime,Price,id)
            cursor.execute(sql % data)
            connect.commit()
            if cursor.rowcount > 0:
                print("修改成功！")
                logging.info("修改数据")
            else:
                print("修改失败！")

# 添加
def add():
    flag=1
    cursor = connect.cursor()
    print("请输入需要添加的信息：")
    while flag==1:
        Tno = input("机票编号：")
        sql = "SELECT * FROM  tickets WHERE Tno=%(pa)s"
        df = pd.read_sql_query(sql, engine, params={"pa": Tno})
        if len(df) > 0:
            print("该编号已经存在，请重新输入！")
        else:
            flag=0

    Hno = input("航班号：")
    StartAdress = input("出发地：")
    EndAdress = input("目的地：")
    StratTime = input("起飞时间：")
    EndTime = input("到达时间：")
    Price = input("票价：")
    TicketType=input("舱位类型：")
    sql = "INSERT INTO tickets VALUES('%s','%s','%s','%s','%s','%s','%s','%s')"
    data = (Tno,Hno, StartAdress, EndAdress, StratTime, EndTime, Price,TicketType)
    cursor.execute(sql % data)
    connect.commit()
    if cursor.rowcount > 0:
        print("添加成功！")
        logging.info("添加数据")
    else:
        print("添加失败！")

#删除
def delete():
    a=1
    while a==1:
        id = input("请输入你要删除的机票编号：")
        sql="SELECT * FROM  tickets WHERE Tno=%(pa)s"
        df = pd.read_sql_query(sql, engine,params={"pa":id})
        if len(df)<=0:
            print("没有该票信息，请重新输入！")
        else:
            a=0
            cursor=connect.cursor()
            sql='delete from tickets where Tno=%s'
            data=(id,)
            cursor.execute(sql % data)
            connect.commit()
            if cursor.rowcount>0:
                print("删除成功！")
                logging.info("删除数据")
            else:
                print("删除失败！")

#主页
def index():

    print("-----------航班机票信息管理系统页面-----------")
    a=1
    try:
        while(a==1):
            print("业务选择如下：")
            print("1.查询机票信息")
            print("2.修改机票信息")
            print("3.添加机票信息")
            print("4.删除机票信息")
            print("5.退出")
            num=input("请选择你的业务操作(输入数字选项)：")
            if num=="1":
                search()
            elif num=="2":
                update()
            elif num=="3":
                add()
            elif num=="4":
                delete()
            elif num=="5":
                a=0
            else:
                print("请输入正确的选择!")
        print("---------------系统已退出------------------")
    except Exception as e:
        logging.error(e)





login()
connect.close()




